<?php

require_once 'IOperations.php';
require_once 'Connexion.class.php';
require_once '../modele/Utilisateur.class.php';
require_once '../modele/Projet.class.php';

class AccesDonneesProjet implements IOperations
{
    private $projet;
    private $utilisateur;
    private $connexionBDD;
    private $initConnexion;
    
    public function __construct ($projet, $utilisateur)
    {
	$this->projet = $projet;
	$this->utilisateur = $utilisateur;
	try
	{
	    $this->initConnexion = new Connexion();
	    $this->connexionBDD = $this->initConnexion->connexionBDD();
	}
	catch (PDOException $erreur)
	{
	    echo 'Erreur, classe : ' . __CLASS__ .  ', méthode : ' . __METHOD__;
	}
    }
    
    public function creation()
    {
	$id = $this->utilisateur->getUsrID();
	$date_creation = $this->projet->getPrjDateCreation();
	$nom = $this->projet->getPrjNom();
	$commentaire = $this->projet->getPrjCommentaire();
	
	$requetePreparee = $this->connexionBDD->prepare("INSERT INTO s_gestion_projet.v_infos_t_projet_prj
			    (usr_id, prj_date_creation, prj_nom, prj_commentaire)
			    VALUES (?, ?, ?, ?)");
	
	$requetePreparee->bindParam(1,$id);
	$requetePreparee->bindParam(2,$date_creation);
	$requetePreparee->bindParam(3,$nom);
	$requetePreparee->bindParam(4,$commentaire);
				
	$execRequeteSQL = $requetePreparee->execute();
	
	return $execRequeteSQL;
    }
    
    public function selection()
    {
	$id = $this->utilisateur->getUsrID();
	
	$requetePreparee = $this->connexionBDD->prepare("SELECT * FROM s_gestion_projet.v_infos_t_projet_prj
							WHERE usr_id = ?");
	
	$requetePreparee->bindParam(1,$id);
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);	
	
	return $resultatSQL;
    }
    
    public function selectionProjetsActivite()
    {
	$id = $this->utilisateur->getUsrID();
	
	$requetePreparee = $this->connexionBDD->prepare("SELECT	usr_id,
								prj_id,
								to_char(prj_date_creation,'DD-MM-YYYY HH24:MI:SS') AS prj_date_creation,
								prj_nom,
								prj_commentaire
							FROM s_gestion_projet.v_infos_t_projet_prj
							WHERE usr_id = ?");
	
	$requetePreparee->bindParam(1,$id);
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);	

	return array($resultatSQL);
    }
    
    //Créer une vue qui regroupe projets, activites, taches pour l'administrateur.
    public function selectionProjetsUtilisateurs()
    {
	$requetePreparee = $this->connexionBDD->prepare("SELECT usr_id,
								prj_id,
								to_char(prj_date_creation,'DD-MM-YYYY HH24:MI:SS') AS prj_date_creation,
								prj_nom,
								prj_commentaire
								FROM s_gestion_projet.v_infos_t_projet_prj ORDER BY prj_date_creation DESC");
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);	
	$tableauProjetSelection = array(array());
    
	$i = 0;
	foreach ($resultatSQL as $ligne)
	{   
	    $tableauProjetSelection[$i]['usr_id'] = $ligne['usr_id'];
	    $tableauProjetSelection[$i]['prj_id'] = $ligne['prj_id'];
	    $tableauProjetSelection[$i]['prj_date_creation'] = $ligne['prj_date_creation'];
	    $tableauProjetSelection[$i]['prj_nom'] = $ligne['prj_nom'];
	    $tableauProjetSelection[$i]['prj_commentaire'] = $ligne['prj_commentaire'];
	    
	    $i++;
	}
	
	return $tableauProjetSelection;
    }
    
    public function selectionStatistiques ()
    {
	$requetePreparee = $this->connexionBDD->prepare("SELECT * FROM s_gestion_projet.v_stats_nb_projets");
	$statistiques = array();
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);
	$statistiques['nb_projets'] = $resultatSQL[0]['nb_projets'];
    
	$requetePreparee = $this->connexionBDD->prepare("SELECT * FROM s_gestion_projet.v_stats_nb_activites");
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);	
	$statistiques['nb_activites'] = $resultatSQL[0]['nb_activites'];
	
	$requetePreparee = $this->connexionBDD->prepare("SELECT * FROM s_gestion_projet.v_stats_nb_taches");
	
	$execRequeteSQL = $requetePreparee->execute();
	$resultatSQL = $requetePreparee->fetchAll(PDO::FETCH_ASSOC);	
	$statistiques['nb_taches'] = $resultatSQL[0]['nb_taches'];
	
	return $statistiques;
    }
    
    public function suppression()
    {
	$id = $this->projet->getPrjID();
				
	$requetePreparee = $this->connexionBDD->prepare("DELETE FROM s_gestion_projet.v_infos_t_projet_prj
	WHERE prj_id = ?");
	
	$requetePreparee->bindParam(1,$id);
	
	$execRequeteSQL = $requetePreparee->execute();
	
	return $execRequeteSQL;
    }
    
    public function miseAJour()
    {
	$date_creation = $this->projet->getPrjDateCreation();
	$nom = $this->projet->getPrjNom();
	$commentaire = $this->projet->getPrjCommentaire();
	$id = $this->projet->getPrjID();
	
	$requetePreparee = $this->connexionBDD->prepare("UPDATE s_gestion_projet.v_infos_t_projet_prj
				SET prj_date_creation = ?,
				prj_nom = ?,
				prj_commentaire = ?		
				WHERE prj_id = ?");
	
	$requetePreparee->bindParam(1,$date_creation);
	$requetePreparee->bindParam(2,$nom);
	$requetePreparee->bindParam(3,$commentaire);
	$requetePreparee->bindParam(4,$id);
				
	$execRequeteSQL = $requetePreparee->execute();
	
	return $execRequeteSQL;
    }
}
?>





